{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Index</th>\n",
       "      <th>CardNo</th>\n",
       "      <th>PeoNo</th>\n",
       "      <th>Money</th>\n",
       "      <th>FundMoney</th>\n",
       "      <th>Surplus</th>\n",
       "      <th>CardCount</th>\n",
       "      <th>conOperNo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.193670e+05</td>\n",
       "      <td>519367.000000</td>\n",
       "      <td>5.193670e+05</td>\n",
       "      <td>519367.000000</td>\n",
       "      <td>519367.000000</td>\n",
       "      <td>519367.000000</td>\n",
       "      <td>519367.000000</td>\n",
       "      <td>251.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.171933e+08</td>\n",
       "      <td>176634.322951</td>\n",
       "      <td>1.664297e+07</td>\n",
       "      <td>4.087279</td>\n",
       "      <td>3.949758</td>\n",
       "      <td>78.495517</td>\n",
       "      <td>1016.565421</td>\n",
       "      <td>154.653386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.542171e+05</td>\n",
       "      <td>12891.317072</td>\n",
       "      <td>7.273705e+06</td>\n",
       "      <td>8.900284</td>\n",
       "      <td>34.329017</td>\n",
       "      <td>121.537938</td>\n",
       "      <td>812.625096</td>\n",
       "      <td>65.489811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.169250e+08</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.170620e+08</td>\n",
       "      <td>172330.000000</td>\n",
       "      <td>2.016565e+07</td>\n",
       "      <td>1.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>34.100000</td>\n",
       "      <td>504.000000</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.171920e+08</td>\n",
       "      <td>180766.000000</td>\n",
       "      <td>2.017561e+07</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>64.500000</td>\n",
       "      <td>749.000000</td>\n",
       "      <td>143.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.173219e+08</td>\n",
       "      <td>183002.000000</td>\n",
       "      <td>2.018301e+07</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>99.390000</td>\n",
       "      <td>1422.000000</td>\n",
       "      <td>201.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.182120e+08</td>\n",
       "      <td>186208.000000</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>900.000000</td>\n",
       "      <td>9800.000000</td>\n",
       "      <td>9903.610000</td>\n",
       "      <td>14575.000000</td>\n",
       "      <td>237.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Index         CardNo         PeoNo          Money  \\\n",
       "count  5.193670e+05  519367.000000  5.193670e+05  519367.000000   \n",
       "mean   1.171933e+08  176634.322951  1.664297e+07       4.087279   \n",
       "std    1.542171e+05   12891.317072  7.273705e+06       8.900284   \n",
       "min    1.169250e+08       9.000000  9.000000e+00       0.000000   \n",
       "25%    1.170620e+08  172330.000000  2.016565e+07       1.200000   \n",
       "50%    1.171920e+08  180766.000000  2.017561e+07       3.000000   \n",
       "75%    1.173219e+08  183002.000000  2.018301e+07       6.000000   \n",
       "max    1.182120e+08  186208.000000  1.000000e+08     900.000000   \n",
       "\n",
       "           FundMoney        Surplus      CardCount   conOperNo  \n",
       "count  519367.000000  519367.000000  519367.000000  251.000000  \n",
       "mean        3.949758      78.495517    1016.565421  154.653386  \n",
       "std        34.329017     121.537938     812.625096   65.489811  \n",
       "min         0.000000       0.000000       1.000000    1.000000  \n",
       "25%         0.000000      34.100000     504.000000  100.000000  \n",
       "50%         0.000000      64.500000     749.000000  143.000000  \n",
       "75%         0.000000      99.390000    1422.000000  201.000000  \n",
       "max      9800.000000    9903.610000   14575.000000  237.000000  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "import numpy as np\n",
    "#读取文件并且进行数据探索\n",
    "data1=pd.read_csv('C:/Users/Q/Desktop/py/project/data1.csv',encoding = \"GBK\")\n",
    "data2=pd.read_csv('C:/Users/Q/Desktop/py/project/data2.csv',encoding = \"GBK\")\n",
    "data2.head()\n",
    "data2.describe()#对data2数据进行总体描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Index</th>\n",
       "      <th>CardNo</th>\n",
       "      <th>AccessCardNo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4341.000000</td>\n",
       "      <td>4341.000000</td>\n",
       "      <td>4.341000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2171.000000</td>\n",
       "      <td>182161.785533</td>\n",
       "      <td>1.679700e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1253.283089</td>\n",
       "      <td>1309.570383</td>\n",
       "      <td>6.891622e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>164340.000000</td>\n",
       "      <td>3.077800e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1086.000000</td>\n",
       "      <td>181084.000000</td>\n",
       "      <td>1.730412e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2171.000000</td>\n",
       "      <td>182169.000000</td>\n",
       "      <td>1.944847e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3256.000000</td>\n",
       "      <td>183254.000000</td>\n",
       "      <td>2.074742e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4341.000000</td>\n",
       "      <td>184339.000000</td>\n",
       "      <td>2.545070e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Index         CardNo  AccessCardNo\n",
       "count  4341.000000    4341.000000  4.341000e+03\n",
       "mean   2171.000000  182161.785533  1.679700e+07\n",
       "std    1253.283089    1309.570383  6.891622e+06\n",
       "min       1.000000  164340.000000  3.077800e+04\n",
       "25%    1086.000000  181084.000000  1.730412e+07\n",
       "50%    2171.000000  182169.000000  1.944847e+07\n",
       "75%    3256.000000  183254.000000  2.074742e+07\n",
       "max    4341.000000  184339.000000  2.545070e+07"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1.head()\n",
    "data1.describe()#对data1数据进行总体描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index           0\n",
      "CardNo          0\n",
      "Sex             0\n",
      "Major           0\n",
      "AccessCardNo    0\n",
      "dtype: int64\n",
      "Index             0\n",
      "CardNo            0\n",
      "PeoNo             0\n",
      "Date              0\n",
      "Money             0\n",
      "FundMoney         0\n",
      "Surplus           0\n",
      "CardCount         0\n",
      "Type              0\n",
      "TermSerNo    512106\n",
      "conOperNo    519116\n",
      "Dept              0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#查看是否有空值\n",
    "print(data1.isnull().sum())\n",
    "print(data2.isnull().sum())\n",
    "data2=data2.drop(['TermSerNo','conOperNo'], axis=1)#去掉data2中没用的空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "0\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "#对data1查询重复数据并清理\n",
    "dupe=data1.duplicated('AccessCardNo')\n",
    "dupe=dupe[dupe]\n",
    "dupe.index\n",
    "data1=data1.drop(dupe.index)#（对data1消除重复数据）\n",
    "data1.to_csv('C:/Users/Q/Desktop/code/task1_1.csv',encoding='utf_8_sig',index=0)\n",
    "print(dupe.sum())\n",
    "dupe=data1.duplicated('CardNo')\n",
    "print(dupe.sum())\n",
    "dupe=data2.duplicated('Index')\n",
    "print(dupe.sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    519367.000000\n",
       "mean          4.087279\n",
       "std           8.900284\n",
       "min           0.000000\n",
       "25%           1.200000\n",
       "50%           3.000000\n",
       "75%           6.000000\n",
       "max         900.000000\n",
       "Name: Money, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2[\"Money\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 640x480 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#money箱型图\n",
    "from matplotlib import pyplot as plt\n",
    "plt.subplot(1,2,1)\n",
    "plt.boxplot(data2[\"Money\"])\n",
    "plt.subplot(1,2,2)\n",
    "plt.boxplot(data2[\"Surplus\"])\n",
    "plt.savefig('money.jpg')\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    519367.000000\n",
       "mean         12.662235\n",
       "std           4.434355\n",
       "min           0.000000\n",
       "25%          10.000000\n",
       "50%          12.000000\n",
       "75%          17.000000\n",
       "max          23.000000\n",
       "Name: hour, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#对时间进行异常值分析，发现数据天在区间30日之内，小时在24小时以内\n",
    "data2['day']=pd.DatetimeIndex(data2['Date']).day\n",
    "data2['day'].describe()\n",
    "data2['hour']=pd.DatetimeIndex(data2['Date']).hour\n",
    "data2['hour'].describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Int64Index([    85,     86,    269,    306,    386,    527,    528,    535,\n",
      "               555,    556,\n",
      "            ...\n",
      "            517256, 517311, 517534, 517828, 518004, 518169, 518506, 518922,\n",
      "            519123, 519281],\n",
      "           dtype='int64', length=5217)\n",
      "519367\n",
      "Int64Index([     4,    269,    299,    306,    308,    310,    325,    326,\n",
      "               364,    410,\n",
      "            ...\n",
      "            518941, 518951, 518981, 519015, 519020, 519023, 519105, 519135,\n",
      "            519192, 519221],\n",
      "           dtype='int64', length=14803)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index        499702\n",
       "CardNo       499702\n",
       "PeoNo        499702\n",
       "Date         499702\n",
       "Money        499702\n",
       "FundMoney    499702\n",
       "Surplus      499702\n",
       "CardCount    499702\n",
       "Type         499702\n",
       "Dept         499702\n",
       "day          499702\n",
       "hour         499702\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间上选取早上5点至晚上22点间算正常值（对data2）\n",
    "#money异常值消除（对data2）\n",
    "a = data2[\"Money\"].quantile(0.75)\n",
    "b = data2[\"Money\"].quantile(0.25)\n",
    "demoney = data2[\"Money\"]\n",
    "de=data2[(demoney>=(a-b)*1.5+a)|(demoney<=b-(a-b)*1.5)].index\n",
    "print(de)\n",
    "c = data2[\"hour\"]\n",
    "print(c.count())\n",
    "d=c[(c<=5)|(c>=22)].index\n",
    "print(d)\n",
    "# data2=data2.drop(de)\n",
    "suoyin=[]\n",
    "for i in range(len(d)):\n",
    "    suoyin.append(d[i])\n",
    "for i in range(len(de)):\n",
    "    suoyin.append(de[i])\n",
    "suoyin=set(suoyin)\n",
    "data2=data2.drop(suoyin)\n",
    "data2.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data2.to_csv('C:/Users/Q/Desktop/code/task1_2.csv',encoding='utf_8_sig',index=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data3=data2.merge(data1,how='inner',on='CardNo')#合并数据表\n",
    "data3.to_csv('C:/Users/Q/Desktop/code/task1_1_1.csv',encoding='utf_8_sig',index=0)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
